﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.GERP.GHRMS.DALFactory;
using KPIS.DBM;

namespace KPIS.GERP.GHRMS.BLL
{
    public class BudgetManagementTestDtlBLL : IBudgetManagementTestDtlBLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        public DataSet ListManagementDtl(int mng_test_seq)
        {
            DataSet dsRet = new DataSet();
            DataSet ds1 = new DataSet();
            DataSet ds2 = new DataSet();
            DataSet ds3 = new DataSet();
            int i = 0;
            int j = 0;
            dbManager.Open();
            dbManager.CreateParameters(2);
            //dbManager.AddParameters(0, "@prm_seq", prm_seq);
            //dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
            dbManager.AddParameters(0, "@mng_test_seq", mng_test_seq);
            dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

            ds3 = dbManager.ExecuteDataSet(CommandType.Text
                    , "select md.mng_test_seq,md.mng_dtls_seq,md.pos_type_seq"
                    + ",pt.pos_type_name,md.sub_sco_level_seq,sl.sub_sco_level_name,md.percen_promote "
                    + " from hrm_prm_management_dtls as md "
                    + " right outer join HRM_MP_POSITION_TYPES as pt on md.pos_type_seq = pt.pos_type_seq "
                    + " right outer join HRM_PRM_SUB_SCORE_LEVELS as sl on md.sub_sco_level_seq = sl.sub_sco_level_seq "
                    + " where md.mng_test_seq = @mng_test_seq"
                    + " and md.record_status = @record_status");
            ds1 = dbManager.ExecuteDataSet(CommandType.Text
                , "select pos_type_seq,pos_type_name from hrm_mp_position_types where record_status = @record_status");
	        string comandstrsql = "select sub_sco_level_seq,sub_sco_level_name"
	                              + " from hrm_prm_sub_score_levels"
	                              + " where record_status = @record_status"
	                              + " and prm_seq = (select m.prm_seq"
	                              + " from hrm_prm_management_test t"
	                              + " inner join hrm_prm_managements m on t.mng_seq = m.mng_seq"
	                              + " where t.mng_test_seq = @mng_test_seq)"
	                              + " and job_site_seq = (select job_site_seq"
	                              + " from hrm_prm_management_test t"
	                              + " inner join hrm_prm_managements m on t.mng_seq = m.mng_seq"
	                              + " where t.mng_test_seq = @mng_test_seq)";
            ds2 = dbManager.ExecuteDataSet(CommandType.Text
				, comandstrsql);

            if (ds3.Tables[0].Rows.Count == 0)
            {
                DataTable myTable = new DataTable();

                myTable.Columns.Add("mng_test_seq");
                myTable.Columns.Add("mng_dtls_seq");
                myTable.Columns.Add("ลำดับที่");
                myTable.Columns.Add("ตำแหน่งประเภท");

                for (i = 0; i <= ds2.Tables[0].Rows.Count - 1; i++)
                {
                    myTable.Columns.Add(ds2.Tables[0].Rows[i].ItemArray[1].ToString());
                }

             
                for (i = 0; i <= ds1.Tables[0].Rows.Count - 1; i++)
                {
                    DataRow dr = myTable.NewRow();
                    dr["mng_test_seq"] = mng_test_seq;
                    dr["mng_dtls_seq"] = 0;
                    dr["ลำดับที่"] = ds1.Tables[0].Rows[i].ItemArray[0];
                    dr["ตำแหน่งประเภท"] = ds1.Tables[0].Rows[i].ItemArray[1];
                  
                    for (j = 0; j <= ds2.Tables[0].Rows.Count - 1; j++)
                    {
                        dr[ds2.Tables[0].Rows[j].ItemArray[1].ToString()] = "";
                    }
                    myTable.Rows.Add(dr);
                }

                dsRet.Tables.Add(myTable);
            }
            else
            {
                DataTable myTable = new DataTable();

                myTable.Columns.Add("mng_test_seq");
                myTable.Columns.Add("mng_dtls_seq");
                myTable.Columns.Add("ลำดับที่");
                myTable.Columns.Add("ตำแหน่งประเภท");

                for (i = 0; i <= ds2.Tables[0].Rows.Count - 1; i++)
                {
                    myTable.Columns.Add(ds2.Tables[0].Rows[i].ItemArray[1].ToString());
                }


                for (i = 0; i <= ds1.Tables[0].Rows.Count - 1; i++)
                {
                    DataRow dr = myTable.NewRow();
                    dr["mng_test_seq"] = mng_test_seq;
                    dr["mng_dtls_seq"] = 0;
                    dr["ลำดับที่"] = ds1.Tables[0].Rows[i].ItemArray[0];
                    dr["ตำแหน่งประเภท"] = ds1.Tables[0].Rows[i].ItemArray[1];
                    for (j = 0; j <= ds3.Tables[0].Rows.Count - 1; j++)
                    {
                        if (ds1.Tables[0].Rows[i].ItemArray[0].ToString() == ds3.Tables[0].Rows[j].ItemArray[2].ToString())
                        {
                            if (ds3.Tables[0].Rows[j].ItemArray[6].ToString() != "")
                            {
                                dr[ds3.Tables[0].Rows[j].ItemArray[5].ToString()] = ds3.Tables[0].Rows[j].ItemArray[6].ToString();
                            }
                            else
                            {
                                dr[ds3.Tables[0].Rows[j].ItemArray[5].ToString()] = 0;
                            }
                        }
                        else
                        {
                            //dr[ds3.Tables[0].Rows[j].ItemArray[5].ToString()] = 0;
                            continue;
                        }
                       
                        
                    }
                    myTable.Rows.Add(dr);
                }

                dsRet.Tables.Add(myTable);
            }

            dbManager.Dispose();
            return dsRet;
        }

        public DataSet ListSubScoreDtl(int mng_test_seq, string pos_type_seq, int prm_seq, int job_site_seq)
        {
            DataSet dsRet = new DataSet();
            DataSet ds1 = new DataSet();
            DataSet ds2 = new DataSet();
            DataSet ds3 = new DataSet();
            int i = 0;
    
            dbManager.Open();
            dbManager.CreateParameters(5);
            dbManager.AddParameters(0, "@mng_test_seq", mng_test_seq);
            dbManager.AddParameters(1, "@pos_type_seq", pos_type_seq);
            dbManager.AddParameters(2, "@prm_seq", prm_seq);
            dbManager.AddParameters(3, "@job_site_seq", job_site_seq);
            dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);

            ds3 = dbManager.ExecuteDataSet(CommandType.Text
                , "select md.mng_test_seq,md.mng_dtls_seq,md.pos_type_seq,pt.pos_type_name"
                + ",md.sub_sco_level_seq,sl.sub_sco_level_name,md.percen_promote "
                + " from hrm_prm_management_dtls as md"
                + ",HRM_MP_POSITION_TYPES as pt"
                + ",HRM_PRM_SUB_SCORE_LEVELS as sl"
                + " where md.pos_type_seq=pt.pos_type_seq"
                    + " and md.sub_sco_level_seq=sl.sub_sco_level_seq "
                    + " and md.mng_test_seq=@mng_test_seq "
                    + " and md.pos_type_seq=@pos_type_seq");
            
            if (ds3.Tables[0].Rows.Count == 0)
            {
                ds1 = dbManager.ExecuteDataSet(CommandType.Text
                    , "select pos_type_seq,pos_type_name"
                    + " from HRM_MP_POSITION_TYPES"
                    + " where pos_type_seq=@pos_type_seq");
                ds2 = dbManager.ExecuteDataSet(CommandType.Text
                    , "select sub_sco_level_seq,sub_sco_level_name"
                    + " from hrm_prm_sub_score_levels"
                    + " where record_status = @record_status"
                    + " and prm_seq = @prm_seq"
                    + " and job_site_seq = @job_site_seq");

                DataTable myTable = new DataTable();

                myTable.Columns.Add("mng_test_seq");
                myTable.Columns.Add("mng_dtls_seq");
                myTable.Columns.Add("pos_type_seq");
                myTable.Columns.Add("pos_type_name");
                myTable.Columns.Add("sub_sco_level_seq");
                myTable.Columns.Add("sub_sco_level_name");
                myTable.Columns.Add("percen_promote");

                for (i = 0; i <= ds2.Tables[0].Rows.Count - 1; i++)
                {
                    DataRow dr = myTable.NewRow();
                    dr["mng_test_seq"] = mng_test_seq;
                    dr["mng_dtls_seq"] = 0;
                    dr["pos_type_seq"] = ds1.Tables[0].Rows[0].ItemArray[0];
                    dr["pos_type_name"] = ds1.Tables[0].Rows[0].ItemArray[1];
                    dr["sub_sco_level_seq"] = ds2.Tables[0].Rows[i].ItemArray[0];
                    dr["sub_sco_level_name"] = ds2.Tables[0].Rows[i].ItemArray[1];
                    dr["percen_promote"] = 0;
                    myTable.Rows.Add(dr);
                }

                dsRet.Tables.Add(myTable);
            }
            else
            {
                dsRet = ds3;
            }



            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return dsRet;
        }

        public DataTable ListPromotionEmployees(int MngTestSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@MngTestSeq", MngTestSeq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

                string strSQL;

                strSQL = "select pe.amount, pe.prm_emp_seq, f.pos_type_seq, pe.emp_seq"
                                + ", pe.prm_job_site_seq, pe.score, p.pos_name, e.class_seq, c.class_name, pe.salary"
                                + ", cast(concat(np.prefix_name, e.first_name, '  ', e.last_name) as char) as emp_text"
                                + ", pe.prm_base_seq, pt.pos_type_name, ss.sub_sco_level_name"
                                + ", b.sal_base, pe.percen_promote, (pe.percen_promote * pe.salary / 100) as percent_amount "
                                + " from hrm_prm_promotion_employees pe"
                                + " left join hrm_ps_employees e on pe.emp_seq = e.emp_seq"
                                + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                                + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                                + " left join hrm_mp_field_positions f on p.field_pos_seq = f.field_pos_seq"
                                + " left join hrm_mp_position_types pt on f.pos_type_seq = pt.pos_type_seq"
                                + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
                                + " left join ( select distinct l.sub_sco_level_seq, sub_sco_level_name"
                                        + ", se.prm_seq, l.sco_max, l.sco_min, l.job_site_seq"
		                                + " from hrm_prm_promotion_employees se"
		                                + " left join hrm_prm_sub_score_levels l on se.prm_seq = l.prm_seq"
		                                + " where (se.SCORE >= l.SCO_MIN AND se.SCORE < l.sco_max + 1 )"
		                        + " )ss on pe.prm_seq = ss.prm_seq"
                                    + " and (pe.SCORE >= ss.SCO_MIN AND pe.SCORE < ss.sco_max + 1 )"
                                    + " and pe.prm_job_site_seq = ss.job_site_seq"
                                + " left join hrm_prm_managements m on pe.prm_seq = m.prm_seq"
                                    + " and pe.prm_job_site_seq = m.job_site_seq"
                                + " left join hrm_prm_management_test mt on m.mng_seq = mt.mng_seq"
                                + " left join hrm_prm_management_dtls md on mt.mng_test_seq = md.mng_test_seq"
                                    + " and f.pos_type_seq = md.pos_type_seq"
                                    + " and ss.sub_sco_level_seq = md.sub_sco_level_seq"
                                + " left join hrm_prm_promotion_bases b on pe.prm_base_seq = b.prm_base_seq"
                                + " where pe.record_status = @record_status"
                                    + " and mt.mng_test_seq = @MngTestSeq";

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable ListManageSummary(int MngTestSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@MngTestSeq", MngTestSeq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

                string strSQL = "SELECT *"
                    + " FROM HRM_PRM_PROMOTION_EMPLOYEES pe"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON pe.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                        //+ " LEFT JOIN HRM_PRM_"
                    + " WHERE pe.RECORD_STATUS = @record_status";

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Insert(int mng_test_seq, int pos_type_seq, int sub_sco_level_seq, Double percen_promote, int CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@mng_test_seq", mng_test_seq);
                dbManager.AddParameters(1, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(2, "@sub_sco_level_seq", sub_sco_level_seq);
                dbManager.AddParameters(3, "@percen_promote", percen_promote);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text
                    , "INSERT INTO HRM_PRM_MANAGEMENT_DTLS"
                    + "(mng_test_seq, pos_type_seq, sub_sco_level_seq, percen_promote, record_status, created_by, CREATED_WHEN)"
                    + " VALUES (@mng_test_seq, @pos_type_seq, @sub_sco_level_seq, @percen_promote, @record_status, @CreatedBy, @CreatedWhen)");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void Update(int mng_dtls_seq, int mng_test_seq, int pos_type_seq, int sub_sco_level_seq, Double percen_promote, int UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@mng_dtls_seq", mng_dtls_seq);
                dbManager.AddParameters(1, "@mng_test_seq", mng_test_seq);
                dbManager.AddParameters(2, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(3, "@sub_sco_level_seq", sub_sco_level_seq);
                dbManager.AddParameters(4, "@percen_promote", percen_promote);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@updated_by", UpdateBy);
                dbManager.AddParameters(7, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text
                    , "UPDATE HRM_PRM_MANAGEMENT_DTLS "
                    + " SET MNG_TEST_SEQ = @mng_test_seq"
                    + ", POS_TYPE_SEQ = @pos_type_seq"
                    + ", SUB_SCO_LEVEL_SEQ = @sub_sco_level_seq"
                    + ", PERCEN_PROMOTE = @percen_promote"
                    + ", PERCEN_PROMOTE = @percen_promote"
                    + ", RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE MNG_DTLS_SEQ=@mng_dtls_seq");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
    }
}
